import datetime

from flask import Blueprint, render_template, session, make_response, send_from_directory
import pymysql
from flask import request
import traceback
from openpyxl import Workbook
import xlrd
import config
from datetime import datetime

def getdataTime():
    # 获取当前日期和时间
    now = datetime.now()

    return str(now.date()) + " " + str(now.hour) + ":" + str(now.minute) + ""


bp=Blueprint('user',__name__,url_prefix='/index')

@bp.route('/appgetuserdata')
def appgetuserdata():
    db = pymysql.connect(host=config.host, port=config.port, user=config.user, password=config.password,
                         database=config.database, charset="utf8")
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select data from tb_user where num=" + request.args.get('num') + " and pwd=" + request.args.get(
        'pwd') + ""

    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(len(results))
        if len(results) == 1:

            data= results[0][0]
            return str(data)
        else:
            return '343'

    except:
        # 如果发生错误则回滚
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()

@bp.route("/appupdatauserdata")
def appupdatauserdata():
    print("修改")

    num = request.args.get('num')
    pwd = request.args.get('pwd')
    data = request.args.get('data')
    data=getdataTime()+'Q'+data
    print(data)


    conn = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    cur = conn.cursor()

    ######

    # SQL 查询语句
    sql = "select data from tb_user where num=" + request.args.get('num') + " and pwd=" + request.args.get(
        'pwd') + ""

    try:
        # 执行sql语句
        cur.execute(sql)
        results = cur.fetchall()
        print(len(results))
        if len(results) == 1:
            data_old = results[0][0]
        else:
            data_old="d"

        print(data_old)
        cur.close()
        conn.close()

    #####

        conn = pymysql.connect(host=config.host, port=config.port, user=config.user, password=config.password,
                               database=config.database,
                               charset="utf8")
        cur = conn.cursor()

        cur.execute('update tb_user set data=%s where num = %s and pwd= %s',(data+'X'+data_old,num,pwd))
        conn.commit()
        cur.close()
        conn.close()
        return "ok"
    except:
        return '失败'
        # 关闭数据库连接
    db.close()


#登录注册二合一
@bp.route("/apploginangsignin")
def apploginangsignin():
    db = pymysql.connect(host=config.host, port=config.port, user=config.user, password=config.password,
                         database=config.database, charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user where num=" + request.args.get('num') + " and pwd=" + request.args.get(
        'pwd') + ""
    print(request.args.get('password'))
    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(len(results))
        if len(results) == 1:


            return '登录成功！'
        else:#注册

            # 获取输入框内容
            num = request.args.get('num')
            password = request.args.get('pwd')

            #判断是否已经存在用户
            sql = "select * from tb_user where num=" + request.args.get('num')
            cursor.execute(sql)
            results = cursor.fetchall()
            if len(results) == 1:
                return '0'#已存在相同用户名

            try:
                # 执行sql语句
                cursor.execute(
                    "INSERT INTO tb_user(num,pwd,data) VALUES (%s,%s,%s)",
                    (num, password, ''))
                # 提交到数据库执行
                db.commit()

                # 注册成功render_template("/web/usermain.html",username=results[0][2],userclass=results[0][4],userfinished=results[0][5],time=results[0][6])
                return '注册成功！'  # render_template("/web/index.html",tip='注册成功！请登录！')
            # return render_template("/web/usermain.html",username=username,userclass=userclass,userfinished=0,time='0时0分')
            except:
                # 抛出错误信息
                traceback.print_exc()
                # 如果发生错误则回滚
                db.rollback()
                return '注册失败'
            # 关闭数据库连接
            db.close()


        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()


@bp.route('/')
def login():
    # 如果已经登录就直接返回
    user_num = session.get('num')
    userclass_se = session.get('userclass')
    print(user_num)
    print(userclass_se)
    if user_num:
        db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                             charset="utf8")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        sql = "select * from tb_user where num=" + user_num
        try:
            # 执行sql语句
            cursor.execute(sql)
            results = cursor.fetchall()
            print(len(results))
            if len(results) == 1:
                session.permanent = True
                session.permanent_session_lifetime = datetime.timedelta(seconds=300 * 60)
                session['username'] = results[0][2]
                session['userclass'] = results[0][4]
                session['num'] = results[0][1]
                print(results[0][4])

                user_time = int(results[0][6])
                hour = int(user_time / 60)
                minute = user_time - hour * 60

                return render_template("/web/usermain.html", username=results[0][2], userclass=results[0][4],
                                       userfinished=results[0][5], hour=hour, minute=minute)  # 返回需要跳转的页面或需要显示的字符串
            else:
                print('失败')
                return '用户名或密码不正确'
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()
    return render_template("/web/index.html")

#注销
@bp.route('/exit')
def exit():
    del session['username']
    del session['userclass']
    del session['num']
    return '已注销'

@bp.route('/login',methods=["get", "post"])
def getLoginRequest():

    #如果已经登录就直接返回
    user_num=session.get('num')
    userclass_se=session.get('userclass')
    print(user_num)
    print(userclass_se)
    if user_num:
        db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                             charset="utf8")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询语句
        sql = "select * from tb_user where num=" + user_num

        try:
            # 执行sql语句
            cursor.execute(sql)
            results = cursor.fetchall()
            print(len(results))
            if len(results) == 1:
                session.permanent = True
                session.permanent_session_lifetime = datetime.timedelta(seconds=300 * 60)
                session['username'] = results[0][2]
                session['userclass'] = results[0][4]
                session['num'] = results[0][1]
                print(results[0][4])

                user_time = int(results[0][6])
                hour = int(user_time / 60)
                minute = user_time - hour * 60

                return render_template("/web/usermain.html", username=results[0][2], userclass=results[0][4],
                                       userfinished=results[0][5], hour=hour, minute=minute)  # 返回需要跳转的页面或需要显示的字符串
            else:
                print('失败')
                return '用户名或密码不正确'
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            traceback.print_exc()
            db.rollback()
        # 关闭数据库连接
        db.close()



    db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user where num=" + request.form.get('num') + " and pwd=" + request.form.get(
        'password') + ""
    print(request.args.get('password'))
    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(len(results))
        if len(results) == 1:
            session.permanent=True
            session.permanent_session_lifetime = datetime.timedelta(seconds=300 * 60)
            session['username']=results[0][2]
            session['userclass']=results[0][4]
            session['num']=results[0][1]
            print(results[0][4])

            user_time=int(results[0][6])
            hour=int(user_time/60)
            minute=user_time-hour*60


            return render_template("/web/usermain.html",username=results[0][2],userclass=results[0][4],userfinished=results[0][5],hour=hour,minute=minute)      #返回需要跳转的页面或需要显示的字符串
        else:
            print('失败')
            return '用户名或密码不正确'
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()

@bp.route('/regist',methods=["get", "post"])
def getRigistRequest():
        # 把用户名和密码注册到数据库中

        # 连接数据库,此前在数据库中创建数据库flask
        db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,charset="utf8")
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # 获取输入框内容
        num = request.form.get('num')
        password = request.form.get('password')
        password2 = request.form.get('password2')
        username=request.form.get('username')
        userclass=request.form.get('class')
        print('----')
        print(num)
        print(password)
        print(password2)
        # 判断两次输入密码是否一致，一致则跳转到登录界面，不一致则弹出警告，要求用户重新输入
        if password == password2:
            # SQL 插入语句
            #sql = "INSERT INTO tb_user(num, username,pwd,class) VALUES (%s,%s,s%,%s)"
            #sql = sql % (repr(num), repr(username), repr(password), repr(userclass))
            sql_0 = "INSERT INTO tb_user(num,username,pwd,class) VALUES (%s,%s,%s,%s)"
            sql = sql_0 % (repr(num), repr(username), repr(password), repr(userclass))


            try:
                # 执行sql语句
                cursor.execute(sql)
                # 提交到数据库执行
                db.commit()
                session.permanent = True
                session.permanent_session_lifetime = datetime.timedelta(seconds=300 * 60)
                session['username'] = username
                session['userclass'] =userclass
                session['num'] = num
                # 注册成功render_template("/web/usermain.html",username=results[0][2],userclass=results[0][4],userfinished=results[0][5],time=results[0][6])
                return render_template("/web/index.html",tip='注册成功！请登录！')
               # return render_template("/web/usermain.html",username=username,userclass=userclass,userfinished=0,time='0时0分')
            except:
                # 抛出错误信息
                traceback.print_exc()
                # 如果发生错误则回滚
                db.rollback()
                return '注册失败'
            # 关闭数据库连接
            db.close()
        else:
            return '两次密码不一致'

def addfinished(name):
    db = pymysql.connect(host=config.host, port=config.port,user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    num=session.get('num')
    print(session.get('username'))
    # SQL 查询语句
    sql = "select * from tb_user where num=" + session.get('num')

    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        finisheds=str(results[0][7])

        if not finisheds.__contains__(name):
            finisheds+='$'+str(name)
            count = finisheds.split('$')
            sql_0 = "update tb_user set"+" finisheds='" + finisheds + "',finished='" + str(
                len(count)-1 ) + "' where num='" + str(session.get('num'))+"'"
            #sql = sql_0 % (repr(num), repr(username), repr(password), repr(userclass))
            try:
                # 执行sql语句
                cursor.execute(sql_0)
                # 提交到数据库执行
                db.commit()

            except:
                traceback.print_exc()
                db.rollback()
    except:
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()


def addtime():
    db = pymysql.connect(host=config.host, port=config.port,user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user where num=" + session.get('num')

    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        time=int(results[0][6])

        if True:
            time+=1
            sql_0 = "update tb_user set"+" onlinetime='" + str(time) + "' where num='" + str(session.get('num'))+"'"
            #sql = sql_0 % (repr(num), repr(username), repr(password), repr(userclass))
            try:
                # 执行sql语句
                cursor.execute(sql_0)
                # 提交到数据库执行
                db.commit()

            except:
                traceback.print_exc()
                db.rollback()
    except:
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()


@bp.route('/getdata')
def getData():
    p=int(request.args.get('p'))
    db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user where num=" + session.get('num')

    try:
        # 执行sql语句
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        result=str(results[0][p])

    except:
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()
    return result




def getResult():
    db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user where num=" + session.get('num')

    try:
        # 执行sql语句
        cursor.execute(sql)
        result = cursor.fetchall()
    except:
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()
    return result



@bp.route('/beat', methods=["get", "post"])
def heartbeat():
    print('跳动'+session.get('num'))
    addtime()


@bp.route('/updataum', methods=["get", "post"])
def updataum():
        print('更新' + session.get('num'))
        res=getResult()
        print(res)
        res=str(res[0][5])+'&'+str(res[0][6])
        print(res)
        return res


@bp.route('/getalldata', methods=["get", "post"])
def getalldata():
    db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,
                         charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from tb_user"

    try:
        # 执行sql语句
        cursor.execute(sql)
        result = cursor.fetchall()
    except:
        traceback.print_exc()
        db.rollback()
    # 关闭数据库连接
    db.close()
    print(result)
    save_to_excel(result)
    try:
        response = make_response(
            send_from_directory('./static/img', '学生学习记录.xls', as_attachment=True))
        return response
    except Exception as e:
        return 'ERROR'
    #return result


# 保存至本地excel表格
def save_to_excel(data):
    outwb = Workbook()
    outws = outwb.worksheets[0]
    i=0
    outws.append(['学号','姓名','班级','已完成实验数目','在线时长'])
    # 遍历外层列表
    for new_dict in data:
        a_list = []
        # 遍历内层每一个字典dict，把dict每一个值存入list
        for item in new_dict:
            i+=1
            if i == 8:
                i=0
                break
            if i==4 or i==1:
                continue
            if i==7:
                time=int(item)
                hour=int(time/60)
                minute=time-hour*60
                a_list.append(str(hour)+'时'+str(minute)+'分')
            else:
                a_list.append(item)
        # sheet直接append list即可
        outws.append(a_list)
    outwb.save('./static/img/学生学习记录.xls')
    print('数据存入excel成功')


@bp.route('/upload')
def upload():
    return render_template("/web/upload.html")






@bp.route('/uploadfile',methods = ['GET','POST'])
def toExcel():
    if request.method == 'POST':
        file = request.files.get('file')
        f = file.read()
        clinic_file = xlrd.open_workbook(file_contents=f)
        # sheet1
        table = clinic_file.sheet_by_index(0)
        nrows = table.nrows
        res=insertdata(table)
        for i in range(1, nrows):
            row_date = table.row_values(i)
            print(row_date)
            #res=insertdata(row_date)

    return str(res)


def insertdata(data):
    flag=True
    nrows = data.nrows
    db = pymysql.connect(host=config.host,port=config.port, user=config.user, password=config.password, database=config.database,charset="utf8")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    print(data)
    # 遍历外层列表
    for i in range(1, nrows):
        row_date = data.row_values(i)
        print(row_date)
        sql_0 = "INSERT INTO tb_user(num,username,pwd,class) VALUES (%s,%s,%s,%s)"
        s=str(row_date[0])
        if s.__contains__('.'):
            ss=s.split('.')
            s=ss[0]
        sql = sql_0 % (repr(s), repr(row_date[1]), repr(row_date[2]), repr(row_date[3]))
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            # 抛出错误信息
            traceback.print_exc()
            # 如果发生错误则回滚
            db.rollback()
            flag=False



    # 关闭数据库连接
    db.close()
    if flag:
       return '写入数据库成功'
    else:
        return '写入数据库失败'







